﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.OleDb;
using HotelManager.Data_Transfer_Layer;

namespace HotelManager.Data_Access_Layer
{
    class hoadonDAO
    {
        #region Variables
        private OleDbConnection conn;
        private string error;
        #endregion

        #region Properties
        public string Error
        {
            get { return error; }
            set { error = value; }
        }
        #endregion

        public hoadonDAO()
        {
            error = "";
            try
            {
                string strConn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=.\\hotel.accdb";
                conn = new OleDbConnection(strConn);
                conn.Open();
            }
            catch (Exception ex)
            {
                Error = "Không kết nối được đến cơ sở dữ liêu";
            }
        }

        public hoadonDTO[] get()
        {
            hoadonDTO[] objs;
            objs = new hoadonDTO[100];
            try
            {
                string sql = "SELECT * FROM hoadon";
                OleDbDataAdapter adapter = new OleDbDataAdapter(sql, conn);
                DataTable dt = new DataTable();
                adapter.Fill(dt);
                OleDbCommand cmd = new OleDbCommand(sql, conn);
                OleDbDataReader reader = cmd.ExecuteReader();
                int i = 0, n = dt.Rows.Count;
                objs = new hoadonDTO[n];
                while (reader.Read())
                {
                    objs[i] = new hoadonDTO();
                    objs[i].Hd_id = reader.GetInt32(0);
                    objs[i].Kh_id = reader.GetInt32(1);
                    objs[i].Hd_ngaydatphong = reader.GetString(2).ToString();                    
                    objs[i].Hd_ngaytraphong = reader.GetString(3).ToString();
                    objs[i].Hd_tongsotien = reader.GetInt32(4);
                    objs[i].Hd_tratruoc = reader.GetInt32(5);
                    objs[i].Hd_ghichu = reader.GetString(6).ToString();
                    objs[i].Hd_khuyenmai = reader.GetInt32(7);
                    i++;
                }
            }
            catch (Exception ex)
            {

            }
            return objs;
        }

        public hoadonDTO View_Detail(int hd_id)
        {
            hoadonDTO obj = new hoadonDTO();
            try
            {
                string sql = "SELECT * FROM hoadon WHERE hd_id=" + hd_id;
                OleDbCommand cmd = new OleDbCommand(sql, conn);
                OleDbDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    obj.Kh_id = reader.GetInt32(1);
                    obj.Hd_ngaydatphong = reader.GetString(2).ToString();
                    obj.Hd_ngaytraphong = reader.GetString(3).ToString();
                    obj.Hd_tongsotien = reader.GetInt32(4);
                    obj.Hd_tratruoc = reader.GetInt32(5);
                    obj.Hd_ghichu = reader.GetString(6).ToString();
                    obj.Hd_khuyenmai = reader.GetInt32(7);
                }
            }
            catch (Exception ex)
            {
                Error = ex.ToString();
            }
            return obj;
        }

        public void Delete(hoadonDTO obj)
        {
            try
            {
                string sql = "DELETE FROM hoadon WHERE hd_id=" + obj.Hd_id;
                OleDbCommand cmd = new OleDbCommand(sql, conn);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Error = ex.ToString();
            }
        }

        public void Insert(hoadonDTO obj)
        {
            try
            {
                string sql = "INSERT INTO hoadon(kh_id,hd_ngaydatphong,hd_ngaytraphong,hd_tongsotien,hd_tratruoc,hd_khuyenmai,hd_ghichu,hd_songaydukien) VALUES(" + obj.Kh_id + ",'" + obj.Hd_ngaydatphong + "','" + obj.Hd_ngaytraphong + "'," + obj.Hd_tongsotien + "," + obj.Hd_tratruoc + "," + obj.Hd_khuyenmai + ",'" + obj.Hd_ghichu + "',"+obj.Hd_songaydukien+")";
                OleDbCommand cmd = new OleDbCommand(sql, conn);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Error = ex.ToString();
            }
        }

        public void Update(hoadonDTO obj)
        {
            try
            {
                string sql1 = "UPDATE hoadon SET hd_ngaydatphong='" + obj.Hd_ngaydatphong + "',hd_ngaytraphong='" + obj.Hd_ngaytraphong + "',hd_tongsotien=" + obj.Hd_tongsotien + ",hd_tratruoc=" + obj.Hd_tratruoc + " WHERE hd_id=" + obj.Hd_id;
                OleDbCommand cmd = new OleDbCommand(sql1, conn);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Error = ex.ToString();
            }
        }

    }
}
